package com.lemon.components.docs.mysql;

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.DataSource;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.lemon.components.ant.beans.ProjectInfo;
import com.lemon.components.ant.project.ProjectMaker;
import com.lemon.components.ant.project.beans.ColumnInfo;
import com.lemon.components.ant.project.beans.EnumInfo;
import com.lemon.components.ant.project.beans.TableInfo;
import com.system.core.security.NameUtil;

public class MysqlLocalTool {
	
	private static List<ColumnInfo> columns = null;
	private static List<TableInfo> tables = null;
	private static DataSource tableInfoDataSource = null;
	private static DataSource dataSource = null;
	
	public static void createdb(ProjectInfo projectInfo) {
		String[] dbInfo = projectInfo.getDbhost().split(":");
		String host = dbInfo[0];
		String port;
		if(dbInfo.length == 1) {
			port = "3306";
		} else {
			port = projectInfo.getDbhost().split(":")[1];
		}
		String cmd = "mysqladmin -u "+projectInfo.getDbusername()+" --password="+projectInfo.getDbpasswd()+" -h "+host+" -P "+port+" create "+projectInfo.getDbname();
		System.out.println("创建数据库："+cmd);
		try {
			Runtime.getRuntime().exec("cmd /c "+cmd, null, new File(projectInfo.getProjectPath()));
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public static void importSQL(ProjectInfo projectInfo) {
		String host = projectInfo.getDbhost().split(":")[0];
		String port = projectInfo.getDbhost().split(":")[1];
		String sqlFilePath = ProjectMaker.class.getResource("/code/maven-project-template/sql/init.sql").getFile();
		sqlFilePath = StringUtils.right(sqlFilePath, sqlFilePath.length()-1);
		String cmd = "mysql -u "+projectInfo.getDbusername()+" --password="+projectInfo.getDbpasswd()+" -h "+host+" -P "+port+" "+projectInfo.getDbname()+" < "+sqlFilePath;
		System.out.println("导入SQL文件："+cmd);
		try {
			Process p = Runtime.getRuntime().exec("cmd /c "+cmd, null, new File(projectInfo.getProjectPath()));
			BufferedReader br = new BufferedReader(new InputStreamReader(p.getInputStream(),"gbk"), 1024);
			String tmp;
			while((tmp = br.readLine())!=null){
				System.out.println(tmp);
			}
			p.waitFor();
			p.destroy();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private static DataSource getDataSourceAbstract(ProjectInfo projectInfo,String conStr) throws Exception {
		Properties prop = new Properties();
		prop.put("driverClassName", projectInfo.getDbdrivername());
		prop.put("characterEncoding", "utf8mb4");
		prop.put("username", projectInfo.getDbusername());
		prop.put("password", projectInfo.getDbpasswd());
		prop.put("initialSize", "5");
		prop.put("maxActive", "10");
		prop.put("maxWait", "3000");
		prop.put("url", conStr);
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
		return dataSource;
	}
	
	public static DataSource getDataSource(ProjectInfo projectInfo) throws Exception {
		String conStr = "jdbc:mysql://"+projectInfo.getDbhost()+"/"+projectInfo.getDbname()+"?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
		return getDataSourceAbstract(projectInfo,conStr);
	}
	
	public static DataSource getDataSourceDBInfo(ProjectInfo projectInfo) throws Exception {
		String conStr = projectInfo.getDbinfo()+"&allowPublicKeyRetrieval=true&serverTimezone=UTC";
		return getDataSourceAbstract(projectInfo,conStr);
	}
	
	public static Connection getCon(ProjectInfo projectInfo) {
		Connection conn = null;
	    try {
	        Class.forName(projectInfo.getDbdrivername());
	        conn = DriverManager.getConnection("jdbc:mysql://"+projectInfo.getDbhost()+"/"+projectInfo.getDbname()+"?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC", projectInfo.getDbusername(), projectInfo.getDbpasswd());
	    } catch (Exception e) {
	        e.printStackTrace();
	    }
	    return conn;
	}
	
	public static Connection getConWithException(ProjectInfo projectInfo) throws Exception {
		Class.forName(projectInfo.getDbdrivername());
		Connection conn = DriverManager.getConnection("jdbc:mysql://"+projectInfo.getDbhost()+"/"+projectInfo.getDbname()+"?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC", projectInfo.getDbusername(), projectInfo.getDbpasswd());
	    return conn;
	}
	
	public static Connection getDBInfoCon(ProjectInfo projectInfo) {
		Connection conn = null;
	    try {
	        Class.forName(projectInfo.getDbdrivername());
	        conn = DriverManager.getConnection(projectInfo.getDbinfo()+"&allowPublicKeyRetrieval=true&serverTimezone=UTC", projectInfo.getDbusername(), projectInfo.getDbpasswd());
	    } catch (Exception e) {
	        e.printStackTrace();
	    }
	    return conn;
	}
	
	public static TableInfo getTableInfo(ProjectInfo projectInfo,String tableName) {
		List<TableInfo> tables = getTableInfo(projectInfo);
		TableInfo tableInfo = null;
		for(TableInfo t:tables) {
			if (t.getTableName().equalsIgnoreCase(tableName)) {
				tableInfo = t;
			}
		}
		return tableInfo;
	}
	
	public static List<TableInfo> getTableInfo(ProjectInfo projectInfo){
		if(tables!=null && tables.size()>0) {
			return tables;
		}
		tables = new ArrayList<TableInfo>();
		StringBuilder tablesSql = new StringBuilder("SELECT * FROM `TABLES` WHERE TABLE_SCHEMA='");
		tablesSql.append(projectInfo.getDbname());
		tablesSql.append("'");
//		tablesSql.append("' GROUP BY TABLE_NAME");
		try {
			if(tableInfoDataSource == null)
				tableInfoDataSource = getDataSourceDBInfo(projectInfo);
		} catch (Exception e) {
			e.printStackTrace();
		}
//		try(Connection conn = getDBInfoCon(projectInfo)){
		try(Connection conn = tableInfoDataSource.getConnection()){
			PreparedStatement preparedStatement = conn.prepareStatement(tablesSql.toString());
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				TableInfo tableInfo = new TableInfo();
				tableInfo.setTableName(resultSet.getString("TABLE_NAME"));
				tableInfo.setTableComment(resultSet.getString("TABLE_COMMENT"));
				List<ColumnInfo> list2 = getColumnInfo(projectInfo,tableInfo.getTableName());
				tableInfo.setColumns(list2);
				try {
					list2.forEach(columnInfo -> {
						if(columnInfo.isPrikey()) {
							tableInfo.setKeyName(columnInfo.getColumnCode());
							tableInfo.setKeyType(columnInfo.getDataType());
						}
					});
				} catch (Exception e) {
					e.printStackTrace();
				}
				
				tables.add(tableInfo);
			}
		}catch(Exception e) {
			e.printStackTrace();
			return null;
		}
		return tables;
	}
	private static Pattern pattern = Pattern.compile("'.*'");
	private static Pattern tinyIntLength = Pattern.compile("tinyint\\((.*)\\)");
//	public static void main(String[] args) {
//		String tmp1 = "tinyint(1)";
//		String tmp2 = "tinyint(1)";
//		Matcher matcher = tinyIntLength.matcher(tmp2);
//		if(matcher.find()) {
//			System.out.println(matcher.group(1));
//		} else {
//			System.out.println("未找到");
//		}
//	}
	
	/**
	 * 传入数据tables，过滤筛选符合表（tableCode）和列属性（columnCode）的列
	 * @param tables
	 * @param tableCode
	 * @param columnCode
	 * @return
	 */
	public static ColumnInfo getColumnInfo(List<TableInfo> tables,String tableCode,String columnCode){
		
		for(TableInfo table:tables) {
			
			if(table.getTableName().equals(tableCode)) {
				for(ColumnInfo column : table.getColumns()) {
					if(column.getColumnCode().equals(ColumnInfo.trim(columnCode))) {
						return column;
					}
				}
			}
			
		}
		System.err.println("表："+tableCode+",属性："+columnCode+"，未找到");
		return null;
	}
	
	public static List<ColumnInfo> getColumnInfo(ProjectInfo projectInfo,String ... tableName){
		System.out.println("加载表的列信息信息："+ArrayUtils.toString(tableName));
		columns = new ArrayList<ColumnInfo>();
		StringBuilder tableNameStr = new StringBuilder();
		for(int i=0;i<tableName.length;i++) {
			tableNameStr.append("'"+tableName[i]+"'");
			if(i<tableName.length-1) {
				tableNameStr.append(",");
			}
		}
		try {
			if(tableInfoDataSource == null)
				tableInfoDataSource = getDataSource(projectInfo);
		} catch (Exception e) {
			e.printStackTrace();
		}
//		try(Connection conn = getDBInfoCon(projectInfo)){
		try(Connection conn = tableInfoDataSource.getConnection()){
			String columnsSql = "SELECT * FROM `COLUMNS` WHERE TABLE_NAME IN ("+tableNameStr+") AND TABLE_SCHEMA = '"+projectInfo.getDbname()+"';";
			PreparedStatement preparedStatement = conn.prepareStatement(columnsSql.toString());
			ResultSet resultSet = preparedStatement.executeQuery();
	        while (resultSet.next()) {
	        	ColumnInfo columnInfo = new ColumnInfo();
	        	columnInfo.setTableName(resultSet.getString("TABLE_NAME"));
	        	columnInfo.setTableClazzCode(NameUtil.getClassName(NameUtil._2JavaName(columnInfo.getTableName())));
	        	columnInfo.setColumnRemark(StringUtils.replace(resultSet.getString("COLUMN_COMMENT"), "'", "`") );
	        	columnInfo.setColumnCode(resultSet.getString("COLUMN_NAME"));
	        	columnInfo.setColumnType(resultSet.getString("COLUMN_TYPE"));//varchar(32)等等
	        	columnInfo.setDataType(resultSet.getString("DATA_TYPE"));//varchar enum int long 等等
	        	columnInfo.setNumber(isNumber(resultSet));
	        	columnInfo.setDate(isDate(columnInfo.getDataType()));
	        	if(resultSet.getString("IS_NULLABLE").equalsIgnoreCase("no")) {
	        		columnInfo.setNullAble(false);
	        	} else {
	        		columnInfo.setNullAble(true);
	        	}
	        	columnInfo.setCharacterMaximumLength(resultSet.getLong("CHARACTER_MAXIMUM_LENGTH"));
	        	columnInfo.setDbName(projectInfo.getDbname());
	        	columnInfo.setJavaCode(NameUtil._2JavaName(columnInfo.getColumnCode()));
	        	columnInfo.setProjectInfo(projectInfo);
	        	columnInfo.setEnumInfo(getEnumInfo(resultSet));
	        	Matcher m = pattern.matcher(columnInfo.getColumnType());
				if(m.find()) {
					List<String> list = new ArrayList<>();
					String[] tmp = m.group(0).replaceAll("'", "").split(",");
					boolean yesnoEnum = false;
					for(String s : tmp) {
						list.add(s);
						if(s.equals("YES")||s.equals("NO"))
							yesnoEnum = true;
					}
					if(tmp.length == 2 && yesnoEnum) {
						columnInfo.setYesnoEnum(yesnoEnum);
					}
				}
	        	String prikey = resultSet.getString("COLUMN_KEY");
	        	if(StringUtils.isNotBlank(prikey)&&prikey.equalsIgnoreCase("PRI")) {
	        		columnInfo.setPrikey(true);
	        	}
	        	columns.add(columnInfo);
	        }
		}catch(Exception e) {
			e.printStackTrace();
			return null;
		}
		return columns;
	}
	
	private static EnumInfo getEnumInfo(ResultSet resultSet) throws Exception {
		
		if(resultSet.getString("DATA_TYPE").equalsIgnoreCase("enum")) {
			EnumInfo enumInfo = new EnumInfo();
			Matcher m = pattern.matcher(resultSet.getString("COLUMN_TYPE"));
			if(m.find()) {
				List<String> list = new ArrayList<String>();
				String[] tmp = m.group(0).replaceAll("'", "").split(",");
				boolean yesnoEnum = false;
				for(String s : tmp) {
					list.add(s);
				}
				enumInfo.setEnumValues(list);
				return enumInfo;
			} else {
				return null;
			}
		} else {
			return null;
		}
		
	}
	
	public static boolean isNumber(ResultSet resultSet) throws Exception {
		String type = resultSet.getString("DATA_TYPE");
		switch (type) {
		case "int":
		case "bigint":
		case "double":
		case "float":
		case "decimal":
			return true;
		case "tinyint":{
			String type2 = resultSet.getString("COLUMN_TYPE");
			Matcher matcher = tinyIntLength.matcher(type2);
			if(matcher.find()) {
				String tinyintLength = matcher.group(1);
				if(StringUtils.isNotBlank(tinyintLength)
						&& Integer.parseInt(tinyintLength)==1) {
					return false;
				}
			}
			return true;
		}
		default:
			return false;
		}
	}
	
	public static boolean isDate(String type) {
		switch (type) {
		case "datetime":
		case "timestamp":
			return true;
		default:
			return false;
		}
	}
}
